-- @owner: @kyeleze
-- @date: 2022/5/16
-- @testpoint: 间隔分区表不支持整型时间戳，类型转换隐含调用插入数据成功

--step1:建立自定义类型转换函数;expect:创建函数成功
create or replace function timestampinteger_to_date
(
    in integer
)
returns date
as
$$
declare
count date;
begin
select to_timestamp($1) into count;
return count;
end;
$$
language plpgsql;
/

--step2:创建转换关系;expect:创建转换关系成功
drop cast if exists (integer as date) cascade;
create cast(integer as date) with function timestampinteger_to_date(integer) as assignment;


--step3:创建间隔分区表;expect:建表成功
drop table if exists t_create_cast_0073 cascade;
create table t_create_cast_0073
(
 cust_id number,
 time_id date
)
partition by range (time_id)
interval('1 day')
( partition p1 values less than ('2019-02-01 00:00:00'),
  partition p2 values less than ('2019-02-02 00:00:00')
);

--step4:隐含调用插入数据;expect:插入数据成功
insert into t_create_cast_0073 values(1001, 1651922190);
select * from t_create_cast_0073;

--step5:清理环境;expect:清理成功
drop cast if exists (integer as date) cascade;
drop function if exists timestampinteger_to_date(integer) cascade;
drop table if exists t_create_cast_0073 cascade;
